/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facades;

import entities.Candidate;
import entities.Company;
import entities.CompanyFolder;
import entities.Province;
import entities.Search;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

/**
 *
 * @author damfercer
 */
@Stateless
public class CandidateFacade extends AbstractFacade<Candidate> {
    @PersistenceContext(unitName = "ProyectoPU")
    private EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public CandidateFacade() {
        super(Candidate.class);
    }
    
    public List<Candidate> getPreviewSearchCandidates(String sql,Integer count,Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        //ESTA CONSULTA SACA LOS PERFILES NO COMPRADOS
        /*sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL " 
                + "AND candidate.ID_CANDIDATE NOT IN(SELECT CANDIDATE FROM company_adquired_profile WHERE COMPANY=");
        sb.append(c.getIdCompany());
        sb.append(")");*/
        sb.append(sql);
        sb.append(" ORDER BY user.CREATION_DATE DESC, candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC");
        sb.append(" LIMIT ");
        sb.append(count.toString());
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        return searchCandidatesQuery.getResultList();
    }
    
    
    public List<Candidate> getSearchCandidates(String sql,Integer count,Integer numberOfCandidates,Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        //ESTA CONSULTA SACA LOS PERFILES NO COMPRADOS
        /*sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND candidate.ID_CANDIDATE NOT IN(SELECT CANDIDATE FROM company_adquired_profile WHERE COMPANY=");
        sb.append(c.getIdCompany());
        sb.append(")");*/
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        sb.append(" LIMIT ").append(count.toString()).append(",").append(numberOfCandidates);
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        return searchCandidatesQuery.getResultList();
    }
    
    
    public List<Candidate> getSearchCandidates(String sql,Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        //ESTA CONSULTA SACA LOS PERFILES NO COMPRADOS
        /*sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND candidate.ID_CANDIDATE NOT IN(SELECT CANDIDATE FROM company_adquired_profile WHERE COMPANY=");
        sb.append(c.getIdCompany());
        sb.append(")");*/
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        return searchCandidatesQuery.getResultList();
    }
    
    
    
    public List<Candidate> getPreviewNewSearchCandidates(String sql, Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        //ESTA CONSULTA SACA LOS PERFILES NO COMPRADOS
        /*sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND candidate.ID_CANDIDATE NOT IN(SELECT CANDIDATE FROM company_adquired_profile WHERE COMPANY=");
        sb.append(c.getIdCompany());
        sb.append(")");*/
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        searchCandidatesQuery.setMaxResults(6);
        return searchCandidatesQuery.getResultList();
    }
    
    public Integer getNumberOfValidCandidates(String sql, Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        
        //ESTA CONSULTA SACA LOS PERFILES NO COMPRADOS
        /*sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND candidate.ID_CANDIDATE NOT IN(SELECT CANDIDATE FROM company_adquired_profile WHERE COMPANY=");
        sb.append(c.getIdCompany());
        sb.append(")");*/
        sb.append(sql);
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
    
    public Integer getNumberOfValidCandidatesFromLastSearchView(String sql, Company c,Search search){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        sb.append("AND user.CREATION_DATE > '");
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sb.append(df.format(search.getLastSearchDate()));
        sb.append("' ");
        sb.append(sql);
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
    
    
    
    public Integer getCandidatesOnlyEmailCount(){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) " +
                    "FROM `candidate` LEFT JOIN `candidate_language_info` ON `candidate_language_info`.CANDIDATE=`candidate`.ID_CANDIDATE " +
                    "WHERE `NAME` IS NULL " +
                    "AND `candidate_language_info`.VIDEO IS NULL " +
                    "AND `LAST_NAME` IS NULL " +
                    "AND `BIRTH_DAY` IS NULL " +
                    "AND `SEX` IS NULL " +
                    "AND `PHONE` IS NULL " +
                    "AND `COUNTRY` IS NULL " +
                    "AND `PROVINCE` IS NULL " +
                    "AND `TOWN` IS NULL " +
                    "AND `TIME_AVAILABILITY` IS NULL " +
                    "AND `HAS_VEHICLE` IS NULL " +
                    "AND `BUST_COLOR` IS NULL");
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
    
    public Integer getCandidatesOnlyEmailAndInfoCount(){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) "+ 
                    "FROM `candidate` LEFT JOIN `candidate_language_info` ON `candidate_language_info`.CANDIDATE=`candidate`.ID_CANDIDATE "+
                    "WHERE `NAME` IS NOT NULL "+
                    "AND `candidate_language_info`.VIDEO IS NULL "+
                    "AND `LAST_NAME` IS NOT NULL "+
                    "AND `BIRTH_DAY` IS NOT NULL "+
                    "AND `SEX` IS NOT NULL "+
                    "AND `PHONE` IS NOT NULL "+
                    "AND `COUNTRY` IS NOT NULL "+
                    "AND `PROVINCE` IS NOT NULL "+
                    "AND `TOWN` IS NOT NULL "+
                    "AND `TIME_AVAILABILITY` IS NOT NULL "+
                    "AND `BUST_COLOR` IS NULL" );
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
    
        public Integer getValidCandidatesWithoutVideo(){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) " +
                    "FROM `candidate` LEFT JOIN `candidate_language_info` ON `candidate_language_info`.CANDIDATE=`candidate`.ID_CANDIDATE " +
                    "WHERE `NAME` IS NOT NULL " +
                    "AND `candidate_language_info`.VIDEO IS NULL " +
                    "AND `LAST_NAME` IS NOT NULL " +
                    "AND `BIRTH_DAY` IS NOT NULL " +
                    "AND `SEX` IS NOT NULL " +
                    "AND `PHONE` IS NOT NULL " +
                    "AND `COUNTRY` IS NOT NULL " +
                    "AND `PROVINCE` IS NOT NULL " +
                    "AND `TOWN` IS NOT NULL " +
                    "AND `TIME_AVAILABILITY` IS NOT NULL " +
                    "AND `BUST_COLOR` IS NOT NULL " );
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
        
        
    
        public Integer getValidCandidatesWithVideo(){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) AS candidatosValidosConVideo " +
                    "FROM `candidate` LEFT JOIN `candidate_language_info` ON `candidate_language_info`.CANDIDATE=`candidate`.ID_CANDIDATE " +
                    "WHERE `NAME` IS NOT NULL " +
                    "AND `candidate_language_info`.VIDEO IS NOT NULL " +
                    "AND `LAST_NAME` IS NOT NULL " +
                    "AND `BIRTH_DAY` IS NOT NULL " +
                    "AND `SEX` IS NOT NULL " +
                    "AND `PHONE` IS NOT NULL " +
                    "AND `COUNTRY` IS NOT NULL " +
                    "AND `PROVINCE` IS NOT NULL " +
                    "AND `TOWN` IS NOT NULL " +
                    "AND `TIME_AVAILABILITY` IS NOT NULL " +
                    "AND `BUST_COLOR` IS NOT NULL " );
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
    
    
    public List<Candidate> getFilteredCandidates(String sql,Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND candidate.ID_CANDIDATE IN(SELECT CANDIDATE FROM company_adquired_profile WHERE COMPANY=");
        sb.append(c.getIdCompany());
        sb.append(")");
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        return searchCandidatesQuery.getResultList();
    }
    
    public List<Candidate> getFilteredCandidates(String sql,CompanyFolder c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND candidate.ID_CANDIDATE IN(SELECT CANDIDATE FROM company_adquired_profile WHERE FOLDER=");
        sb.append(c.getIdCompanyFolder());
        sb.append(")");
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        return searchCandidatesQuery.getResultList();
    }
    
    public List<Candidate> getFilteredCandidates(String sql){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
        sb.append(sql);
        sb.append(" ORDER BY candidate_economic_type.VALUE DESC, candidate.VISITS DESC, user.LAST_DATE DESC, user.LOGINS DESC, candidate.LAST_MODIFICATION_DATE DESC, user.CREATION_DATE DESC");
        Query searchCandidatesQuery=em.createNativeQuery(sb.toString(), Candidate.class);
        return searchCandidatesQuery.getResultList();
    }
    
    public List<Candidate> getCandidatesWithNoPictures(){
        Query namedQuery=em.createNamedQuery("Candidate.findByNoPictures");
        return namedQuery.getResultList();
    }
    
    
    public void refresh(Candidate c){
        if(em.getEntityManagerFactory().getCache().contains(Candidate.class, c.getIdCandidate())){
            em.getEntityManagerFactory().getCache().evict(Candidate.class, c.getIdCandidate());
        }
    }
    
    public Boolean isValidCandidate(Candidate candidate){
        List<Candidate> candidates= getFilteredCandidates("AND candidate.ID_CANDIDATE="+candidate.getIdCandidate());
        return !candidates.isEmpty();
    }
    
    
    public Integer relatedUserFromCompany(Company c){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL "
                + "AND user.RELATED_USER=");
        sb.append(c.getIdUser().getIdUser());
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
    
    public List<Candidate>getInterestedAndValidCandidatesInASearch(String sql,Search search){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT DISTINCT candidate.* FROM candidate_search_interest csi LEFT JOIN candidate  ON candidate.ID_CANDIDATE = csi.CANDIDATE  "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL ");
                sb.append(" ");
                sb.append(sql);
                sb.append(" AND csi.SEARCH=");
                sb.append(search.getIdSearch());
                Query searchCandidatesQuery=em.createNativeQuery(sb.toString(),Candidate.class);
                return searchCandidatesQuery.getResultList();
    }
    
    public Integer getNumberOfValidCandidatesFromProvince(Province province){
        StringBuilder sb=new StringBuilder();
        sb.append("SELECT COUNT(DISTINCT ID_CANDIDATE) FROM candidate "
                + "LEFT JOIN candidate_educational_info ON candidate_educational_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_interest_area ON candidate_interest_area.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_language_info ON candidate_language_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_professional_info ON candidate_professional_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_relocating_info ON candidate_relocating_info.CANDIDATE=candidate.ID_CANDIDATE "
                + "LEFT JOIN candidate_economic_type ON candidate_economic_type.ID_CANDIDATE_ECONOMIC_TYPE=candidate.CANDIDATE_ECONOMIC_TYPE "
                + "LEFT JOIN job_duration ON candidate_professional_info.JOB_DURATION=job_duration.ID_JOB_DURATION "
                + "LEFT JOIN educational_level ON candidate_educational_info.EDUCATIONAL_LEVEL=educational_level.ID_EDUCATIONAL_LEVEL "
                + "LEFT JOIN time_availability ON candidate.TIME_AVAILABILITY=time_availability.ID_TIME_AVAILABILITY "
                + "LEFT JOIN user ON user.ID_USER=candidate.USER "
                + "LEFT JOIN language_level ON candidate_language_info.LANGUAGE_LEVEL=language_level.ID_LANGUAGE_LEVEL "
                + "LEFT JOIN desired_salary ON candidate_interest_area.DESIRED_SALARY=desired_salary.ID_DESIRED_SALARY "
                + "WHERE user.ACTIVE=1 AND candidate.BUST_BLACK_WHITE IS NOT NULL AND candidate.NAME IS NOT NULL AND candidate.LAST_NAME IS NOT NULL AND candidate.BIRTH_DAY IS NOT NULL AND candidate.PROVINCE = ");
        sb.append(province.getIdProvince().toString());
        Query countQuery=em.createNativeQuery(sb.toString());
        long longNum=(Long)countQuery.getSingleResult();
        return (int)longNum;
    }
}
